package com.fingard.dsp.bank.directbank;

import java.sql.Connection;
import java.util.*;

import com.fingard.FGBiz;
import com.fingard.db.JDBCHelper;
import com.fingard.diagnostics.LogHelper;
import com.fingard.dsp.bank.reqrespobj.single.detail.RetAtsSglDetail;

public class DirectHelper {
	
	public static int countReqSeqIDMapping(String p_bankCode, Date p_transDate, StringBuilder p_sb){
		if(FGBiz.dspCenter.dspSet.dbConnectionSet != null){
			try{
				Connection dbConn = null;
				//PreparedStatement pre = null;
				try{
					dbConn = FGBiz.dspCenter.dspSet.dbConnectionSet.getConnection();
					
					//String sqlInsert = "select count(*) from REQSEQIDMAPPING where TransDate=? and BankCode=?";
			        /*
                    pre = dbConn.prepareStatement(sqlInsert);
			        
			        pre.setTimestamp(1, new java.sql.Timestamp(p_transDate.getTime()));
			        pre.setString(2, p_bankCode);
			        ResultSet rs = pre.executeQuery();
			        if(rs.next()){
			        	return rs.getInt(1);
			        }*/
                    Map<String,Object> reqSeqIDMap=JDBCHelper.execQuerySingle(dbConn,"select count(*) CC from REQSEQIDMAPPING where TransDate=? and BankCode=?",new Object[]{new java.sql.Timestamp(p_transDate.getTime()),p_bankCode});
                    if(reqSeqIDMap!=null&&reqSeqIDMap.size()>0){
                    	Object objCount = reqSeqIDMap.get("CC");
                        return Integer.parseInt(String.valueOf(objCount));
                    }
			        p_sb.append("查询为空");
				}catch(Exception ex){
					p_sb.append(LogHelper.getStackTrace(ex));
					ex.printStackTrace();
				}
				finally{
					//if (pre != null)
		                //pre.close();
					if(dbConn != null){
						dbConn.close();
					}
				}
			}catch(Exception ex){
				p_sb.append(LogHelper.getStackTrace(ex));
				ex.printStackTrace();
			}
		}
		else{
			p_sb.append("数据库连接未配置或有误");
		}
		return -1;
	}
	
	public static String getReqSeqIDMapping(String p_atsSeqID) throws Exception{
		if(FGBiz.dspCenter.dspSet.dbConnectionSet != null){

			Connection dbConn = null;
			//PreparedStatement pre = null;
			try{
				dbConn = FGBiz.dspCenter.dspSet.dbConnectionSet.getConnection();
				
				//String sqlSelect = "select ToBankSeqID from REQSEQIDMAPPING where AtsSeqId=?";
		        /*
                pre = dbConn.prepareStatement(sqlSelect);
		        pre.setString(1, p_atsSeqID);
		        ResultSet rs = pre.executeQuery();
		        if(rs.next()){
		        	return rs.getString(1);
		        }
		        */
                Map<String,Object> reqSeqIDMap=JDBCHelper.execQuerySingle(dbConn,"select TOBANKSEQID from REQSEQIDMAPPING where AtsSeqId=?",new Object[]{p_atsSeqID});
                if(reqSeqIDMap!=null&&reqSeqIDMap.size()>0){
                    return String.valueOf(reqSeqIDMap.get("TOBANKSEQID"));
                }
		        return "";
			}catch(Exception ex){					
				throw(ex);
			}
			finally{
				//if (pre != null)
	                //pre.close();
				if(dbConn != null){
					dbConn.close();
				}
			}
		}
		else{
			throw new Exception("数据库连接未配置或有误");
		}
	}

    public static String getAtsSeqIDMapping(Date p_transDate,String p_toBankSeqID) throws Exception{
        if(FGBiz.dspCenter.dspSet.dbConnectionSet != null){

            Connection dbConn = null;
            //PreparedStatement pre = null;
            try{
                dbConn = FGBiz.dspCenter.dspSet.dbConnectionSet.getConnection();

                Map<String,Object> reqSeqIDMap=JDBCHelper.execQuerySingle(dbConn,"select AtsSeqId from REQSEQIDMAPPING where TransDate=? and TOBANKSEQID=?",new Object[]{new java.sql.Timestamp(p_transDate.getTime()),p_toBankSeqID});
                if(reqSeqIDMap!=null&&reqSeqIDMap.size()>0){
                    return String.valueOf(reqSeqIDMap.get("ATSSEQID"));
                }
                return "";
            }catch(Exception ex){
                throw(ex);
            }
            finally{
                //if (pre != null)
                //pre.close();
                if(dbConn != null){
                    dbConn.close();
                }
            }
        }
        else{
            throw new Exception("数据库连接未配置或有误");
        }
    }

	public static boolean saveReqSeqIDMapping(String p_bankCode,String p_transType, Date p_transDate, String p_atsSeqID, String p_toBankSeqID, StringBuilder p_sb){
		if(FGBiz.dspCenter.dspSet.dbConnectionSet != null){
			try{
				Connection dbConn = null;
				//PreparedStatement pre = null;
				try{
					dbConn = FGBiz.dspCenter.dspSet.dbConnectionSet.getConnection();
					
					//String sqlInsert = "insert into REQSEQIDMAPPING(TransDate,AtsSeqId,ToBankSeqID,TransType,BankCode)values(?,?,?,?,?)";
                    /*
			        pre = dbConn.prepareStatement(sqlInsert);
			        
			        pre.setTimestamp(1, new java.sql.Timestamp(p_transDate.getTime()));
			        pre.setString(2, p_atsSeqID);
			        pre.setString(3, p_toBankSeqID);
			        pre.setString(4, p_transType);
			        pre.setString(5, p_bankCode);
			        pre.execute();
			        */
                    JDBCHelper.update(dbConn,"insert into REQSEQIDMAPPING(TransDate,AtsSeqId,ToBankSeqID,TransType,BankCode)values(?,?,?,?,?)",new Object[]{new java.sql.Timestamp(p_transDate.getTime()),p_atsSeqID,p_toBankSeqID,p_transType,p_bankCode});
			        
			        return true;
					
				}catch(Exception ex){
					p_sb.append(LogHelper.getStackTrace(ex));
					ex.printStackTrace();
				}
				finally{
					//if (pre != null)
		                //pre.close();
					if(dbConn != null){
						dbConn.close();
					}
				}
			}catch(Exception ex){
				p_sb.append(LogHelper.getStackTrace(ex));
				ex.printStackTrace();
			}
		}
		else{
			p_sb.append("数据库连接未配置或有误");
		}
		return false;
	}
	
	public static boolean saveSeqNum(String p_bankCode, String p_atsSeqID, HashMap<String,String> p_mapNum, StringBuilder p_sb){
		if(FGBiz.dspCenter.dspSet.dbConnectionSet != null){
			try{
				Connection dbConn = null;
				//PreparedStatement pre = null;
				try{
					dbConn = FGBiz.dspCenter.dspSet.dbConnectionSet.getConnection();
					
					String sqlInsert = "insert into SEQNUM"+p_bankCode+"(REQSEQID,ASKNUMBER,SEQNUMBER)values(?,?,?)";
                    /*
			        pre = dbConn.prepareStatement(sqlInsert);
			        
			        pre.setString(1, p_atsSeqID);
			        
			        Iterator<String> iter2 = p_mapNum.keySet().iterator();
			        while (iter2.hasNext()) {
			            String eachKey2 = iter2.next();
			            pre.setString(2, eachKey2);
				        pre.setString(3, p_mapNum.get(eachKey2));
				        pre.execute();
			        }*/
                    Iterator<String> iter2 = p_mapNum.keySet().iterator();
                    while (iter2.hasNext()) {
                        String eachKey2 = iter2.next();
                        JDBCHelper.update(dbConn,sqlInsert,new Object[]{p_atsSeqID,eachKey2,p_mapNum.get(eachKey2)});
                    }
			        
			        return true;
					
				}catch(Exception ex){
					p_sb.append(LogHelper.getStackTrace(ex));
					ex.printStackTrace();
				}
				finally{
					//if (pre != null)
		                //pre.close();
					if(dbConn != null){
						dbConn.close();
					}
				}
			}catch(Exception ex){
				p_sb.append(LogHelper.getStackTrace(ex));
				ex.printStackTrace();
			}
		}
		else{
			p_sb.append("数据库连接未配置或有误");
		}
		return false;
	}
	
	public static HashMap<String, String> getSeqNum(String p_bankCode, String p_atsSeqID) throws Exception{
		if(FGBiz.dspCenter.dspSet.dbConnectionSet != null){
			HashMap<String,String> retMap = new HashMap<String,String>();
			Connection dbConn = null;
			//PreparedStatement pre = null;
			try{
				dbConn = FGBiz.dspCenter.dspSet.dbConnectionSet.getConnection();
				
				String sqlSelect = "select seqnumber,asknumber from SEQNUM"+p_bankCode+" where reqseqid=?";
                /*
		        pre = dbConn.prepareStatement(sqlSelect);
		        pre.setString(1, p_atsSeqID);
		        ResultSet rs = pre.executeQuery();
		        while(rs.next()){
		        	retMap.put(rs.getString(1),rs.getString(2));
		        }*/
                List<Map<String,Object>> seqList=JDBCHelper.execQuery(dbConn,sqlSelect,new Object[]{p_atsSeqID});
                if(seqList!=null&&seqList.size()>0){
                    for(Map<String,Object> seqMap:seqList){
                        retMap.put(String.valueOf(seqMap.get("SEQNUMBER")),String.valueOf(seqMap.get("ASKNUMBER")));
                    }
                }
		        return retMap;
			}catch(Exception ex){					
				throw(ex);
			}
			finally{
				//if (pre != null)
	                //pre.close();
				if(dbConn != null){
					dbConn.close();
				}
			}
		}
		else{
			throw new Exception("数据库连接未配置或有误");
		}
	}

	public static boolean saveSinglePayNotice(String p_bankCode, RetAtsSglDetail p_sglDetail) throws Exception{
		if(FGBiz.dspCenter.dspSet.dbConnectionSet != null){
			try{
				Connection dbConn = null;
				try{
					dbConn = FGBiz.dspCenter.dspSet.dbConnectionSet.getConnection();
                    int rowsAff = JDBCHelper.update(dbConn,"insert into NOTICE_"+p_bankCode+"(reqSeqID,respCode,transState,respInfo,payInfoCode,transAmt)values(?,?,?,?,?,?)",new Object[]{p_sglDetail.reqSeqID,p_sglDetail.respCode,p_sglDetail.transState,p_sglDetail.respInfo,p_sglDetail.payInfoCode,p_sglDetail.transAmt});
			        if(rowsAff>0){
			        	return true;
			        }else{
			        	throw new Exception("影响行数为："+rowsAff);
			        }
				}catch(Exception ex){
					throw(ex);
				}
				finally{
					//if (pre != null)
		                //pre.close();
					if(dbConn != null){
						dbConn.close();
					}
				}
			}catch(Exception ex){
				throw(ex);
			}
		}
		else{
			throw new Exception("数据库连接未配置或有误");
		}
	}
	
	public static List<Map<String, Object>> getSinglePayNotice(String p_bankCode, String p_reqSeqIDs_in, String p_orderBy) throws Exception{
		if(FGBiz.dspCenter.dspSet.dbConnectionSet != null){

			Connection dbConn = null;
			try{
				dbConn = FGBiz.dspCenter.dspSet.dbConnectionSet.getConnection();
				List<Map<String, Object>> listMap =JDBCHelper.execQuery(dbConn,"select * from NOTICE_"+p_bankCode+" where reqSeqID in("+p_reqSeqIDs_in+") "+p_orderBy,new Object[]{});
                if(listMap!=null&&listMap.size()>0){
                    return listMap;
                }
		        return null;
			}catch(Exception ex){					
				throw(ex);
			}
			finally{
				if(dbConn != null){
					dbConn.close();
				}
			}
		}
		else{
			throw new Exception("数据库连接未配置或有误");
		}
	}
}
